set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		Wu,Yue
-- Create date: <Create Date,,>
-- Description:	Create 12 months in a table based on current time point
-- =============================================
ALTER FUNCTION [dbo].[Bidding_BuildTimeDemension]
(	
	@TimePoint DATETIME
)
RETURNS @Time TABLE
(
	[Year]   INT,
	[Month]  INT,
	[Day]    INT,
	[DatePoint] DATETIME
) 
AS
BEGIN
	DECLARE @CurrentYear INT;
	DECLARE @MonthCnt INT;
	DECLARE @DayCnt INT;
	SET @CurrentYear=Year(@TimePoint);
	SET @MonthCnt=1;
	
	DECLARE @Month TABLE 
	(
		[Year] INT,
		[Month] INT,
		BeginDay INT,
		EndDay   INT
	)
	
	DECLARE @CurrentMonth DATETIME;
	DECLARE @NextMonth    DATETIME;
	DECLARE @LastDayInMonth INT;
	
	WHILE @MonthCnt<13
	BEGIN
	  SET @CurrentMonth=CAST(@CurrentYear AS NVARCHAR(4))+'-'+CAST(@MonthCnt AS NVARCHAR(2))+'-'+'01';
	  SET @NextMonth=DATEADD(mm,1,@CurrentMonth);
	  SET @LastDayInMonth=DAY(DATEADD(dd,-1,@NextMonth));
	  INSERT INTO @Month([Year],[Month],BeginDay,EndDay)
	  VALUES(@CurrentYear,@MonthCnt,1,@LastDayInMonth);
	  SET @MonthCnt=@MonthCnt+1;
	END
	
	DECLARE @Num TABLE (N BIGINT);
	INSERT INTO @Num(N)
	SELECT N FROM dbo.Bidding_BuildNumbers(1,100);
	
	INSERT INTO @Time([Year],[Month],[Day],DatePoint)
	SELECT m.[Year],m.[Month],n.N,CAST(m.[Year] AS NVARCHAR(4))+'-'+CAST(m.[Month] AS NVARCHAR(2))+'-'+CAST(n.N AS NVARCHAR(2)) AS DatePoint
	FROM @Month m
	INNER JOIN @Num n
	ON n.N BETWEEN m.BeginDay AND m.EndDay
    
	RETURN;
  
END

